#-*-coding:utf-8-*-
# Create your views here.
from crm.models import Role
import role_cfg as rcfg
from crm.models import Agent
from crm.models import Members
from crm.models import Furtherinterest
from crm.models import Projects
from crm.models import Salesperson
from crmv2.models import Student
from models import Follow_up,Next_Follow_Date
from crm.models import Sales_record
#from crm.models import Maillist,Maillog
from django.shortcuts import get_object_or_404,render_to_response
from django.http import Http404, HttpResponse, HttpResponseRedirect
#from django import forms
from django.core.paginator import Paginator, InvalidPage
import time,datetime,check
from django.db import connection 

import logClass as log

log = log.Log()

def afterWeekDate():
    today = datetime.datetime.today()
    aWeekDelta = datetime.timedelta(weeks=1)
    aWeekAgo = today - aWeekDelta
    return datetime.datetime.strftime(aWeekAgo,'%Y-%m-%d')

"""查看roles列表清单"""
def recruitment (request):
    '''发送邮件页面和处理ajax发送邮件'''
    if not check.checklogin(request):
        return HttpResponseRedirect('/')

    is_con = int(request.GET.get('is_con',0))
    begin = request.GET.get('starttime',0)
    end = request.GET.get('endtime',0)
    s_id = int(request.GET.get('s_id',0))

    error_info = ''
    source_ret = []
    source_total = 0
    tl_nums = 0
    source_status={}

    project_ret = []
    project_total = 0
    ptl_nums = 0
    project_status = {}
    
    status_ret = []
    status_total = 0

    cand_ret = []
    cand_ret_temp = {}
    si_num = 0
    li_num = 0
    agent=''

    app_source_ret = []
    app_source_total = 0

    app_project_ret = []
    app_project_total = 0

    enro_source_ret = []
    enro_source_total = 0

    enro_program_ret = []
    enro_program_total = 0

    where = 'where 1=1'
    #members = Members.objects.filter(is_removed=0).order_by('id')

    if not begin:
        begin = afterWeekDate()

    if not end:
        end = datetime.datetime.strftime(datetime.datetime.today(),'%Y-%m-%d')

    if end<begin:
        error_info = 'Start date is not greater than end date'
    
    where += ''' and regtime>="%s" and regtime <="%s"'''%(begin,end)
    try:
        project_list = Projects.objects.order_by('name')
        agent_list = Agent.objects.order_by('id')
        sale_list = Salesperson.objects.order_by('id')
    except Exception,e:
        log.error(e)
    
    if error_info:
        pass;
    else:
        projects = {}
        agents = {}
        for project in project_list:
            projects[project.id]=project.name
        for agent in agent_list:
            agents[agent.id]=agent.name
        del(agent_list)
        del(agent)
        del(project)

        if s_id:
            where += ''' and `salesperson_id`=%s'''%s_id
        try:
            cursor = connection.cursor() 
            
            '''------ 处理By Source数据 ------'''
            source_sql = '''select count(*) as nums,`agent_id` from `crm_members` %s group by `agent_id` order by nums desc'''%where
            source_sql1 = '''select count(*) as nums,`status` from `crm_members` %s and `status` in (8,11) group by `status` order by `status` desc'''%where
            cursor.execute(source_sql) 
            source_sql = cursor.fetchall()
            for sou in source_sql:
                source_total = source_total+int(sou[0])
                if sou[1]==0:
                    source_ret.append(('NoSource',sou[0]))
                    continue;
                source_ret.append((agents[sou[1]],sou[0]))
            tl_nums = source_total
            cursor.execute(source_sql1) 
            source_sql1 = cursor.fetchall()
            for s_status in source_sql1:
                tl_nums -= int(s_status[0])
                source_status[s_status[1]] = s_status[0]
        except Exception,e:
            log.error(e)
        '''------ 处理By Program数据 ------'''
        try:
            program_sql = '''select count(*) as nums,`projects_id` from `crm_members` %s group by `projects_id` order by nums desc'''%where
            program_sql1 = '''select count(*) as nums,`status` from `crm_members` %s and `status`=8 group by `status`'''%where
            cursor.execute(program_sql) 
            program_sql = cursor.fetchall()
            for pro in program_sql:
                project_total = project_total+int(pro[0])
                if not projects.has_key(pro[1]):
                    projects[pro[1]] = 'Other'
                if pro[1]==0:
                    project_ret.append(('NoSource',pro[0]))
                    continue;
                project_ret.append((projects[pro[1]],pro[0]))
            ptl_nums = project_total
            cursor.execute(program_sql1) 
            program_sql1 = cursor.fetchall()
            for p_status in program_sql1:
                ptl_nums -= int(p_status[0])
                project_status[p_status[1]] = p_status[0]
        except Exception,e:
            log.error(e)
        '''------ 处理By Status数据 ------'''
        try:
            statuss = ['Not yet','Can\'t reach','App sch\'d','Under cons','Call later','Conflict','No call','Enrolled','Repeat','Application','Dead','Overseas']
            status_sql = '''select count(*) as nums,`status` from `crm_members` %s group by `status` order by nums desc'''%where
            cursor.execute(status_sql) 
            status_sql = cursor.fetchall()
            for status in status_sql:
                status_total = status_total+int(status[0])
                if status[1]==0:
                    status_ret.append(('NoSource',status[0]))
                    continue;
                status_ret.append((statuss[int(status[1])],status[0]))
        except Exception,e:
            log.error(e)
        '''------ 处理Potential Candidate数据 ------'''
        try:
            interlevel = ['NC','HI','SI','NI','NT','LI']
            cand_sql = '''select `projects_id`,`interlevel`,count(*) as nums from `crm_members` %s and `interlevel` in (2,5) group by `projects_id`,`interlevel` order by `projects_id`,nums desc'''%where
            cursor.execute(cand_sql) 
            cand_sql = cursor.fetchall()
            for cand in cand_sql:
                if not cand_ret_temp.has_key(projects[int(cand[0])]):
                    cand_ret_temp[projects[int(cand[0])]] = [0,0]
                if int(cand[1])==2:
                    si_num += cand[2]
                    cand_ret_temp[projects[int(cand[0])]][0]=cand[2]
                else:
                    li_num += cand[2]
                    cand_ret_temp[projects[int(cand[0])]][1]=cand[2]
                #cand_ret.append((projects[int(cand[0])],interlevel[int(cand[1])],cand[2]))
            for cand in cand_ret_temp:
                cand_ret.append((cand,cand_ret_temp[cand][0],cand_ret_temp[cand][1]))
        except Exception,e:
            log.error(e)
        '''------ 处理New Application By Source数据 ------'''
        try:
            app_source_sql = '''select count(*) as nums,`agent_id` from `crm_members` %s and `status`=9 group by `agent_id` order by nums desc'''%where
            cursor.execute(app_source_sql) 
            app_source_sql = cursor.fetchall()
            for sou in app_source_sql:
                app_source_total = app_source_total+int(sou[0])
                if sou[1]==0:
                    app_source_ret.append(('NoSource',sou[0]))
                    continue;
                app_source_ret.append((agents[sou[1]],sou[0]))
        except Exception,e:
            log.error(e)
        '''------ 处理New Application By Program数据 ------'''
        try:
            app_program_sql = '''select count(*) as nums,`projects_id` from `crm_members` %s and `status`=9 group by `projects_id` order by nums desc'''%where
            cursor.execute(app_program_sql) 
            app_program_sql = cursor.fetchall()
            for pro in app_program_sql:
                app_project_total += int(pro[0])
                if pro[1]==0:
                    app_project_ret.append(('NoSource',pro[0]))
                    continue;
                app_project_ret.append((projects[pro[1]],pro[0]))
        except Exception,e:
            log.error(e)
        '''------ 处理Enrolled By Source数据 ------'''
        try:
            where = '''where a.date1>="%s" and a.date1<="%s"'''%(begin,end)
            if s_id:
                where += ''' and b.`salesperson_id`=%s'''%s_id
            enro_source_sql = '''select count(*) as nums,b.`agent_id` from `student_payment_record` a join `crmv2_student` b on b.id=a.student_id %s group by b.`agent_id` order by nums desc'''%where
            cursor.execute(enro_source_sql) 
            enro_source_sql = cursor.fetchall()
            for sou in enro_source_sql:
                enro_source_total = enro_source_total+int(sou[0])
                if sou[1]==0:
                    enro_source_ret.append(('NoSource',sou[0]))
                    continue;
                enro_source_ret.append((agents[sou[1]],sou[0]))
        except Exception,e:
            log.error(e)
        '''------ 处理Enrolled By Program数据 ------'''
        try:
            where = '''where a.date1>="%s" and a.date1<="%s"'''%(begin,end)
            enro_program_sql = '''select count(*) as nums,a.`project_id` from `student_payment_record` a %s group by a.`project_id` order by nums desc'''%where
            cursor.execute(enro_program_sql) 
            enro_program_sql = cursor.fetchall()
            for pro in enro_program_sql:
                enro_program_total += int(pro[0])
                if pro[1]==0:
                    enro_program_ret.append(('NoProgram',pro[0]))
                    continue;
                enro_program_ret.append((projects[pro[1]],pro[0]))
        except Exception,e:
            log.error(e)
        cursor.close()

    begy,begm,begd = time.strptime(begin,'%Y-%m-%d')[0:3]
    endy,endm,endd = time.strptime(end,'%Y-%m-%d')[0:3]
    begtime = datetime.datetime(begy,begm,begd)
    endtime = datetime.datetime(endy,endm,endd)
    return render_to_response('report/sales/recruitment.html',{
        'begin':begtime,
        'end':endtime,
        'project_list':project_list,
        'is_con':is_con,
        'error_info':error_info,
        'source_ret':source_ret,
        'source_total':source_total,
        'source_status':source_status,
        'tl_nums':tl_nums,
        'project_ret':project_ret,
        'project_total':project_total,
        'ptl_nums':ptl_nums,
        'project_status':project_status,
        'status_ret':status_ret,
        'status_total':status_total,
        'cand_ret':cand_ret,
        'si_num':si_num,
        'li_num':li_num,
        'app_source_ret':app_source_ret,
        'app_source_total':app_source_total,
        'app_project_ret':app_project_ret,
        'app_project_total':app_project_total,
        'enro_source_ret':enro_source_ret,
        'enro_source_total':enro_source_total,
        'enro_program_ret':enro_program_ret,
        'enro_program_total':enro_program_total,
        'sales':sale_list,
        'ttt':''
    })

def sales (request):
    '''发送邮件页面和处理ajax发送邮件'''
    if not check.checklogin(request):
        return HttpResponseRedirect('/')

    is_con = int(request.GET.get('is_con',0))
    begin = request.GET.get('starttime',0)
    end = request.GET.get('endtime',0)
    p_id = int(request.GET.get('p_id',0))
    s_id = int(request.GET.get('s_id',0))

    error_info = ''
    
    sales_ret = []
    tolmoney = {}

    try:
        project_list = Projects.objects.order_by('name')
        sale_list = Salesperson.objects.order_by('id')
    except Exception,e:
        log.error(e)
    where = 'where 1=1'
    #members = Members.objects.filter(is_removed=0).order_by('id')

    if not begin:
        begin = afterWeekDate()

    if not end:
        end = datetime.datetime.strftime(datetime.datetime.today(),'%Y-%m-%d')

    if end<begin:
        error_info = 'Start date is not greater than end date'
    
    if error_info:
        pass;
    else:
        cursor = connection.cursor()
        try:
            sale_list = Salesperson.objects.order_by('id')
        except Exception,e:
            log.error(e)
        projects = {}
        sales = {}
        for project in project_list:
            projects[project.id]=project.name
        for sale in sale_list:
            sales[sale.id]=sale.name
        del(project)
        del(sale)
        
        where += ''' and a.`applytime`>="%s" and a.`applytime`<="%s"'''%(begin,end)
        if p_id:
            where += ''' and b.`project_id`=%s'''%p_id
        if s_id:
            where += ''' and a.`salesperson_id`=%s'''%s_id
        
        try:
            sql1 = '''select b.`project_id` pid,a.`name_en` name,a.`first_name`,a.`last_name`,a.`salesperson_id`,b.`date1`,b.`date1_money`,a.`remark`,b.`moneytypedate1` from `crmv2_student` a join `student_payment_record` b on b.student_id=a.id %s and a.id=b.student_id order by b.`project_id`'''%where
            cursor.execute(sql1) 
            sql = cursor.fetchall()
            temp = {'name':'','value':[],'tolmoney':{},'tolmoneys':''}
            flag = 1
            if len(sql):
                for sale in sql:
                    sale = list(sale)
                    if not projects.has_key(sale[0]):
                        projects[sale[0]] = 'Other'
                    if not sales.has_key(sale[4]):
                        sales[sale[4]] = 'Other'
                    if sale[6]=='':
                        sale[6] = `0`
                    if projects[sale[0]]==temp['name']:
                        temp['value'].append(('%s,%s'%(sale[3],sale[2]),sales[sale[4]],sale[5],'%s %s'%(sale[8],sale[6]),sale[7]))
                    else:
                        if flag==0:
                            temp['tolmoneys'] = `temp['tolmoney']`[1:-1].replace('u','').replace('\'','')
                            sales_ret.append(temp)
                            temp = {'name':'','value':[],'tolmoney':{},'tolmoneys':''}
                        temp['name'] = projects[sale[0]]
                        temp['value'].append(('%s,%s'%(sale[3],sale[2]),sales[sale[4]],sale[5],'%s %s'%(sale[8],sale[6]),sale[7]))
                        flag = 0
                    if temp['tolmoney'].has_key(sale[8]):
                        temp['tolmoney'][sale[8]] += float(sale[6].replace(',',''))
                    else:
                        temp['tolmoney'][sale[8]] = float(sale[6].replace(',',''))
                    if tolmoney.has_key(sale[8]):
                        tolmoney[sale[8]] += float(sale[6].replace(',',''))
                    else:
                        tolmoney[sale[8]] = float(sale[6].replace(',',''))
                for k in temp['tolmoneys']:
                    temp['tolmoneys'][k] = float('%0.2f'%temp['tolmoneys'][k])

                temp['tolmoneys'] = `temp['tolmoney']`[1:-1].replace('u','')
                sales_ret.append(temp)
                #del(temp)
            else:
                sales_ret = ''
                tolmoney = ''
        except Exception,e:
            log.error(e)
        cursor.close()
    begy,begm,begd = time.strptime(begin,'%Y-%m-%d')[0:3]
    endy,endm,endd = time.strptime(end,'%Y-%m-%d')[0:3]
    begtime = datetime.datetime(begy,begm,begd)
    endtime = datetime.datetime(endy,endm,endd)
    return render_to_response('report/sales/sales.html',{
        'begin':begtime,
        'end':endtime,
        'project_list':project_list,
        'sales':sale_list,
        'is_con':is_con,
        'error_info':error_info,
        'sales_ret':sales_ret,
        'tolmoney':`tolmoney`[1:-1].replace('u','').replace('\'',''),
        'ttt':temp['tolmoneys']
    })

def enrollment (request):
    '''发送邮件页面和处理ajax发送邮件'''
    if not check.checklogin(request):
        return HttpResponseRedirect('/')

    is_con = int(request.GET.get('is_con',0))
    begin = request.GET.get('starttime',0)
    end = request.GET.get('endtime',0)
    p_id = int(request.GET.get('p_id',0))

    error_info = ''
    program_ret = []
    sql = ''
    where = ''
    where1 = ''

    try:
        project_list = Projects.objects.order_by('name')
        agent_list = Agent.objects.order_by('id')
    except Exception,e:
        log.error(e)

    if not begin:
        begin = afterWeekDate()

    if not end:
        end = datetime.datetime.strftime(datetime.datetime.today(),'%Y-%m-%d')

    if end<begin:
        error_info = 'Start date is not greater than end date'
    
    if error_info:
        pass;
    else:
        try:
            projects = {}
            for project in project_list:
                projects[project.id]=project.name
            cursor = connection.cursor()
            where += ''' and class_start>="%s" and class_start<="%s"'''%(begin,end)
            if p_id:
                where += ''' and `project_id`=%s'''%p_id
                where1 = ''' and `project_id`=%s'''%p_id
            sql = '''SELECT  `period` , COUNT( * ) FROM  `program_student_course` WHERE 1=1 %s GROUP BY  `period`'''%where1
            cursor.execute(sql) 
            course_ret = cursor.fetchall()
            courses = {}
            for course in course_ret:
                courses[course[0]] = course[1]

            sql1 = '''SELECT `project_id`,`modules`,`lecture`,`venue`,`class_start`,`period` FROM project_course_info WHERE id IN (SELECT MAX( id ) FROM project_course_info where 1=1 %s GROUP BY  `project_id` order by `class_start` ) ORDER BY `class_start` '''%where
            cursor.execute(sql1) 
            pro_ret = cursor.fetchall()
            for pro in pro_ret:
                if courses.has_key(pro[5]):
                    pro6 = courses[pro[5]]
                else:
                    pro6 = 0 
                program_ret.append((pro[4],projects[pro[0]],pro[1],pro[2],pro[3],pro6))
            
        except Exception,e:
            log.error(e)
    
    begy,begm,begd = time.strptime(begin,'%Y-%m-%d')[0:3]
    endy,endm,endd = time.strptime(end,'%Y-%m-%d')[0:3]
    begtime = datetime.datetime(begy,begm,begd)
    endtime = datetime.datetime(endy,endm,endd)
    return render_to_response('report/sales/enrollment.html',{
        'begin':begtime,
        'end':endtime,
        'project_list':project_list,
        'is_con':is_con,
        'pro_ret':program_ret,
        'error_info':error_info,
        'ttt':''
    })

def leads (request):
    '''发送邮件页面和处理ajax发送邮件'''
    if not check.checklogin(request):
        return HttpResponseRedirect('/')

    is_con = int(request.GET.get('is_con',0))
    begin = request.GET.get('starttime',0)
    end = request.GET.get('endtime',0)
    p_id = int(request.GET.get('p_id',0))

    error_info = ''
    total_lead=''
    
    leads = []
    total_static = []

    try:
        project_list = Projects.objects.order_by('name')
        agent_list = Agent.objects.order_by('id')
    except Exception,e:
        log.error(e)
    where = 'where 1=1'
    #members = Members.objects.filter(is_removed=0).order_by('id')

    if not begin:
        begin = afterWeekDate()

    if not end:
        end = datetime.datetime.strftime(datetime.datetime.today(),'%Y-%m-%d')

    if end<begin:
        error_info = 'Start date is not greater than end date'
    
    if error_info:
        pass;
    else:
        try:
            cursor = connection.cursor()
            where += ''' and regtime>="%s" and regtime <="%s"'''%(begin,end)
            if p_id:
                where += ''' and `projects_id`=%s'''%p_id
        
            projects = {}
            agents = {}
            pro_temp = []
            total_lead = 0
            for project in project_list:
                projects[project.id]=project.name.replace(' ','').replace('&','')
                pro_temp.append(project.name.replace(' ','').replace('&',''))
            pro_temp.append('Other');
            for agent in agent_list:
                agents[agent.id]=agent.name
            del(project)
            del(agent)

            sql1 = '''SELECT `agent_id`,`projects_id`,count(`projects_id`) FROM `crm_members` %s group by `agent_id`,`projects_id` order by `agent_id` desc,`projects_id` desc'''%where
            cursor.execute(sql1) 
            sql = cursor.fetchall()
            
            value = []
            for i in pro_temp:
                value.append(0)    
            total_static = value[:]

            temp = {'name':'','value':value[:],'total':0}
            flag = 1
            if len(sql):
                for lead in sql:
                    if not agents.has_key(lead[0]):
                        agents[lead[0]] = 'Other'
                    if not projects.has_key(lead[1]):
                        projects[lead[1]] = 'Other'

                    if agents[lead[0]]==temp['name']:
                        temp['value'][pro_temp.index(projects[lead[1]])] = int(lead[2])
                    else:
                        if flag==0:
                            if not temp['name']=='':
                                leads.append(temp)
                                temp = {'name':'','value':value[:],'total':0}
                        if projects[lead[1]]=='Other':
                            continue
                        temp['name'] = agents[lead[0]]
                        temp['value'][pro_temp.index(projects[lead[1]])] = int(lead[2])
                        flag = 0
                    temp['total'] += int(lead[2])
                    total_lead += int(lead[2])
                    total_static[pro_temp.index(projects[lead[1]])] += int(lead[2])
                if not temp['name']=='':
                    leads.append(temp)
                #del(temp)
            else:
                leads = ''
        except Exception,e:
            log.error(e)
    begy,begm,begd = time.strptime(begin,'%Y-%m-%d')[0:3]
    endy,endm,endd = time.strptime(end,'%Y-%m-%d')[0:3]
    begtime = datetime.datetime(begy,begm,begd)
    endtime = datetime.datetime(endy,endm,endd)
    return render_to_response('report/sales/leads.html',{
        'begin':begtime,
        'end':endtime,
        'project_list':project_list,
        'pro_len':len(project_list),
        'is_con':is_con,
        'leads':leads,
        'total_static':total_static,
        'error_info':error_info,
        'total_lead':total_lead,
        'ttt':''
    })
